package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.google.gson.Gson;
import com.google.gson.JsonParseException;
import com.google.gson.reflect.TypeToken;

import oop.data.History;
import oop.data.SimpleUser;
import oop.db.Database;

public final class HistoryDAO {

	public static int countByUser(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT COUNT(*)" +
				"FROM tblHistory " +
				"WHERE hist_id = ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			rs.next();
			return rs.getInt(1);
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<History> fetchById(int id) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT hist_id, hist_mix, hist_user, user_name, " +
							"hist_date, hist_mark, hist_time, hist_answers " +
					"FROM tblHistory, tblUser " +
					"WHERE hist_user = user_id AND hist_id = ?";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)	rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<History> fetchByUser(int id, int start, int length)
			throws SQLException {
	Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT hist_id, hist_mix, hist_user, user_name, " +
							"hist_date, hist_mark, hist_time, hist_answers " +
					"FROM tblHistory, tblUser " +
					"WHERE hist_user = user_id AND user_id = ? " +
					"ORDER BY hist_date " +
					"LIMIT ?, ?";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			stmt.setInt(2, start);
			stmt.setInt(3, length);
			rs = stmt.executeQuery();
			return fromResultSet(rs);
		} finally {
			if (rs != null)	rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	protected static List<History> fromResultSet(ResultSet rs)
			throws JsonParseException, SQLException {
		List<History> histories = new ArrayList<History>();
		while (rs.next()) {
			histories.add(new History(rs.getInt(1), new SimpleUser(
					rs.getInt(3), rs.getString(4)), rs.getInt(2),
					rs.getDate(5), rs.getDouble(6), rs.getInt(7), fromJson(rs.getString(8))));
		}
		return histories;
	}

	public static History create(SimpleUser user, long mixId, Date takenDate,
			double mark, int time, Map<Long, long[]> choices)
			throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "INSERT INTO `tracnghiem`.`tblHistory` (hist_mix, " +
							"hist_user, hist_date, hist_mark, hist_time, hist_answers) " +
							"values (?, ?, ?, ?, ?, ?)";
			
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, mixId);
			stmt.setInt(2, user.getId());
			stmt.setDate(3, new java.sql.Date(new Date().getTime()));
			stmt.setDouble(4, mark);
			stmt.setInt(5, time);
			stmt.setString(6, toJson(choices));
			stmt.executeUpdate();

			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			int id = rs.getInt(1);

			return new History(id, user, mixId, takenDate, mark, time, choices);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	@SuppressWarnings("unchecked")
	public static Map<Long, long[]> fromJson(String json) {
		return (Map<Long, long[]>) new Gson().fromJson(json,
				new TypeToken<HashMap<Long, long[]>>() { /* NOTHING */
				}.getType());
	}

	public static String toJson(Map<Long, long[]> choices) {
		return new Gson().toJson(choices);
	}

}
